package com.dd.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Hibernate;
import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.type.Type;
import org.springframework.context.ApplicationContext;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.dd.entity.DdFirstPage;
import com.dd.entity.DdShop;
import com.dd.entity.DdTop;
import com.dd.util.PageList;

/**
 * A data access object (DAO) providing persistence and search support for
 * DdShop entities. Transaction control of the save(), update() and delete()
 * operations can directly support Spring container-managed transactions or they
 * can be augmented to handle user-managed Spring transactions. Each of these
 * methods provides additional information for how to configure it for the
 * desired type of transaction control.
 * 
 * @see com.dd.entity.DdShop
 * @author MyEclipse Persistence Tools
 */

public class DdShopDAO extends HibernateDaoSupport {
	private static final Log log = LogFactory.getLog(DdShopDAO.class);
	// property constants
	public static final String TYPE = "type";
	public static final String NAME = "name";
	public static final String SHOPHOURS = "shophours";
	public static final String STYLE = "style";
	public static final String COST = "cost";
	public static final String PHONE = "phone";
	public static final String MOBILE = "mobile";
	public static final String ADDRESS = "address";
	public static final String AREA = "area";
	public static final String ROAD = "road";
	public static final String LANDMARK = "landmark";
	public static final String RANGE = "range";
	public static final String BUS = "bus";
	public static final String PARKING = "parking";
	public static final String DESCRIPTION = "description";
	public static final String PURPOSE = "purpose";
	public static final String SERVICE = "service";
	public static final String NUMBER = "number";
	public static final String NUMBERTYPE = "numbertype";
	public static final String BELONG = "belong";

	protected void initDao() {
		// do nothing
	}

	public void saveOrUpdateDT(DdTop dt) {
		log.debug("saving DdTop instance");
		try {
			getHibernateTemplate().saveOrUpdate(dt);

		} catch (Exception re) {
			re.printStackTrace();
		}
	}

	public PageList findTops(int skip, int count) {
		try {
			PageList pl = new PageList();

			String maxsizeStr = ("select count(*) from Dd_Top where types!=0");
			Connection conn = this.getSession().connection();
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(maxsizeStr);
			int max = 0;
			while (rs.next()) {
				max = rs.getInt(1);
			}
			Query q = this.getSession().createQuery(
					"from DdTop where types!=0 order by id desc");
			q.setFirstResult(skip);
			q.setMaxResults(count);
			List list = q.list();
			pl.addAll(list);
			pl.setTotalCount(max);
			return pl;
		} catch (Exception re) {
			re.printStackTrace();
			return new PageList();
		}

	}

	public void saveOrUpdate(DdShop transientInstance) {
		log.debug("saving DdShop instance");
		try {
			getHibernateTemplate().saveOrUpdate(transientInstance);
			log.debug("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			re.printStackTrace();
			throw re;
		}
	}

	public void delete(DdShop persistentInstance) {
		log.debug("deleting DdShop instance");
		try {
			getHibernateTemplate().delete(persistentInstance);
			log.debug("delete successful");
		} catch (RuntimeException re) {
			log.error("delete failed", re);
			throw re;
		}
	}

	public DdShop findById(java.lang.Integer id) {
		log.debug("getting DdShop instance with id: " + id);
		try {
			DdShop instance = (DdShop) getHibernateTemplate().get(
					"com.dd.entity.DdShop", id);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}

	public List findByProperty(String propertyName, Object value) {
		log.debug("finding DdShop instance with property: " + propertyName
				+ ", value: " + value);
		try {
			String queryString = "from DdShop as model where model."
					+ propertyName + "= ?";
			return getHibernateTemplate().find(queryString, value);
		} catch (RuntimeException re) {
			log.error("find by property name failed", re);
			throw re;
		}
	}

	public List findByType(Object type) {
		return findByProperty(TYPE, type);
	}

	public List findByName(Object name) {
		return getHibernateTemplate().find(
				"from DdShop where name like '%" + name
						+ "%' and alive=1 order by id desc");
	}

	public List findByBelong(Object belong) {
		return findByProperty(BELONG, belong);
	}

	public List findByName(String name, int isnew) {
		return getHibernateTemplate().find(
				"from DdShop where name='" + name + "'");
	}

	// 根据坐标象限找所有商家
	public List findAllByPos(String posx1, String posy1, String posx2,
			String posy2, String sending, String booking, String discount,
			String shopstyle, String shopcost, String test) {
		log.debug("findAllByPos ");
		try {
			String detailsql = "and alive=1 ";
			if (test != null && test.equals("1")) {
				detailsql = "and (alive=1 or isnew=1) ";
			}
			if (sending.equals("1")) {
				detailsql = detailsql + "and issend=1 ";
			}
			if (booking.equals("1")) {
				detailsql = detailsql + "and isbooking=1 ";
			}
			if (discount.equals("1")) {
				detailsql = detailsql + "and iscoupon=1 ";
			}
			// 1私房菜,2自助餐,3火锅,4川湘,5粤,6闽,7西餐厅,8日本,9泰国,10韩国

			if (shopstyle != null && !shopstyle.equals("0")) {
				if (shopstyle.equals("1")) {
					detailsql = detailsql
							+ "and styletype=6 and 134217728&style=134217728 ";
				} else if (shopstyle.equals("2")) {
					detailsql = detailsql + "and styletype=8 and 4&style=4 ";
				} else if (shopstyle.equals("3")) {
					detailsql = detailsql + "and styletype=6 and 4&style=4 ";
				} else if (shopstyle.equals("4")) {
					detailsql = detailsql
							+ "and styletype=6 and (2&style=2 or 16&style=16) ";
				} else if (shopstyle.equals("5")) {
					detailsql = detailsql + "and styletype=6 and 8&style=8 ";
				} else if (shopstyle.equals("6")) {
					detailsql = detailsql + "and styletype=6 and 1&style=1 ";
				} else if (shopstyle.equals("7")) {
					detailsql = detailsql + "and styletype=5 ";
				} else if (shopstyle.equals("8")) {
					detailsql = detailsql + "and styletype=7 and 2&style=2 ";
				} else if (shopstyle.equals("9")) {
					detailsql = detailsql + "and styletype=7 and 8&style=8 ";
				} else if (shopstyle.equals("10")) {
					detailsql = detailsql + "and styletype=7 and 1&style=1 ";
				}

			}
			if (shopcost != null && !shopcost.equals("0")) {
				detailsql = detailsql + "and cost=" + shopcost + " ";
			}

			return getHibernateTemplate().find(
					"from DdShop where posx>" + posx1 + " and posx<" + posx2
							+ " and  posy>" + posy1 + " and posy<" + posy2
							+ " and types=1 " + detailsql
							+ " order by isbooking desc");
		} catch (RuntimeException re) {
			log.error("findAllByPos failed", re);
			throw re;
		}
	}

	// 根据google坐标象限找所有商家
	public List findAllByPos_g(String x0,String y0,String posx1, String posy1, String posx2,
			String posy2, String sending, String booking, String discount) {
		log.debug("findAllByPos ");
		try {

			String detailsql = "and alive=1 and isnew=1 ";

			if (sending.equals("1")) {
				detailsql = detailsql + "and issend=1 ";
			}
			if (booking.equals("1")) {
				detailsql = detailsql + "and isbooking=1 ";
			}
			if (discount.equals("1")) {
				detailsql = detailsql + "and iscoupon=1 ";
			}
			String orderby = "order by asin(sqrt(pow(sin((posx_g-"+x0+")/2),2)+cos(posx_g*3.141592653589793/180)*cos("+x0+"*3.141592653589793/180)*pow(sin((posy_g-"+y0+")/2),2))) asc";
			System.out.println("select *  from dd_shop where posx_g>0 and  posx_g>"
					+ posx1
					+ " and posx_g<"
					+ posx2
					+ " and  posy_g>"
					+ posy1
					+ " and posy_g<"
					+ posy2
					+ " and types=1 "
					+ detailsql
					+ " order by SQRT((posx_g-"+x0+")*(posx_g-"+x0+")+(posy_g-"+y0+")*(posx_g-"+y0+")) asc");

//			return getHibernateTemplate().find("from DdShop where posx_g>0 and  posx_g>"
//									+ posx1
//									+ " and posx_g<"
//									+ posx2
//									+ " and  posy_g>"
//									+ posy1
//									+ " and posy_g<"
//									+ posy2
//									+ " and types=1 "
//									+ detailsql
//									+ " order by SQRT((posx_g-"+x0+")*(posx_g-"+x0+")+(posy_g-"+y0+")*(posx_g-"+y0+")) asc");
			return getHibernateTemplate().find("from DdShop where posx_g>0 and  posx_g>"
					+ posx1
					+ " and posx_g<"
					+ posx2
					+ " and  posy_g>"
					+ posy1
					+ " and posy_g<"
					+ posy2
					+ " and types=1 "
					+ detailsql+orderby);
//					+ " order by ABS((posx_g-"+x0+")*(posx_g-"+x0+")+(posy_g-"+y0+")*(posx_g-"+y0+")) asc");

		} catch (Exception re) {
			re.printStackTrace();
			return null;
		}
	}

	// 根据名字模糊查找所有商家
	public List findAllByShopname(String shopname, String test) {
		log.debug("findAllByPos ");
		try {
			String detailsql = "and alive=1 ";
			if (test != null && test.equals("1")) {
				detailsql = "and (alive=1 or isnew=1) ";
			}
			return getHibernateTemplate().find(
					"from DdShop where name like'%" + shopname
							+ "%' and types=1 " + detailsql);
		} catch (RuntimeException re) {
			log.error("findAllByPos failed", re);
			throw re;
		}
	}

	public List findAll() {
		log.debug("finding all DdShop instances");
		try {
			String queryString = "from DdShop where types=1";
			return getHibernateTemplate().find(queryString);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	public List findAll1() {
		log.debug("finding all DdShop instances");
		try {
			String queryString = "from DdShop";
			return getHibernateTemplate().find(queryString);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	public List findAllOrdeyByID() {
		log.debug("finding all DdShop instances");
		try {
			String queryString = "from DdShop where alive=1 and isbooking=1 order by id desc ";
			return getHibernateTemplate().find(queryString);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}

	public PageList findAll(String sqlwhere, int pageNo, int pageSize) {
		log.debug("finding all DdOrder instances");
		try {
			PageList pl = new PageList();
			String queryString = "from DdShop a where 1=1 " + sqlwhere;
			String maxsizeStr = (" select count(*) " + queryString);
			Query q = this.getSession().createQuery(maxsizeStr);
			int max = 0;
			List l = q.list();
			if (l != null && !l.isEmpty()) {
				max = (Integer) l.get(0);
			}
			q = this.getSession().createQuery(
					queryString + " order by a.id desc");
			q.setFirstResult((pageNo - 1) * pageSize);
			q.setMaxResults(pageSize);
			List list = q.list();
			pl.addAll(list);
			pl.setTotalCount(max);
			return pl;
		} catch (RuntimeException re) {
			re.printStackTrace();
			log.error("find all failed", re);
			throw re;
		}
	}

	public PageList findbypage(String query, int pageNo, int pageSize,
			String addQuery, String orderby, int types) {
		log.debug("finding findbypage DdShopgoods instances");
		try {
			long l1 = System.currentTimeMillis();
			if (orderby == null || orderby.equals("")) {
				orderby = " order by discount asc, hot desc,id desc";
			}
			StringBuffer additionalQuery = new StringBuffer("");
			if (query != null && !query.trim().equals("")) {
				// query = query.replaceAll("\\p{P}", "");
				String tem_str[] = query.split(" ");
				for (int i = 0; i < tem_str.length; i++) {
					if (tem_str[i].trim().equals("")) {
						continue;
					}
					additionalQuery.append(" and (name like '%"
							+ tem_str[i].trim() + "%' or address like '%"
							+ tem_str[i].trim() + "%' or landmark like '%"
							+ tem_str[i].trim() + "%' or purposevalue like '%"
							+ tem_str[i].trim() + "%' or stylevalue like '%"
							+ tem_str[i].trim() + "%' or suggest like '%"
							+ tem_str[i].trim() + "%' )");
				}
			}
			PageList pl = new PageList();
			String queryString = "from DdShop where shopstyle=1 and alive=1 and types="
					+ types + " " + additionalQuery.toString() + " " + addQuery;
			String maxsizeStr = (" select count(*) " + queryString);
			Connection conn = this.getSession().connection();
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(maxsizeStr.replace("DdShop",
					"Dd_Shop"));
			int max = 0;
			while (rs.next()) {
				max = rs.getInt(1);
			}
			queryString = queryString + " " + orderby;
			Query q = this.getSession().createQuery(queryString);
			q.setFirstResult((pageNo - 1) * pageSize);
			q.setMaxResults(pageSize);
			List list = q.list();
			pl.addAll(list);
			pl.setTotalCount(max);
			queryString = queryString.substring(0, queryString
					.indexOf("order by"));
			pl.setQueryString(queryString.replace("from DdShop", ""));
			// System.out.println("findbypage use "
			// + (System.currentTimeMillis() - l1));
			return pl;
		} catch (Exception re) {
			log.error("findbypage failed", re);
			return null;
		}
	}

	public PageList findbypage1(String query, int pageNo, int pageSize,
			String addQuery, String orderby, int types) {
		log.debug("finding  DdShopgoods instances");
		try {
			long l1 = System.currentTimeMillis();
			if (orderby == null || orderby.equals("")) {
				orderby = " order by id desc";
			}
			StringBuffer additionalQuery = new StringBuffer("");
			if (query != null && !query.trim().equals("")) {
				// query = query.replaceAll("\\p{P}", "");
				String tem_str[] = query.split(" ");
				for (int i = 0; i < tem_str.length; i++) {
					if (tem_str[i].trim().equals("")) {
						continue;
					}
					additionalQuery.append(" and (name like '%"
							+ tem_str[i].trim() + "%' or sendtovalue like '%"
							+ tem_str[i].trim() + "%' or purposevalue like '%"
							+ tem_str[i].trim() + "%' or stylevalue like '%"
							+ tem_str[i].trim() + "%' or shopname like '%"
							+ tem_str[i].trim() + "%' )");
				}
			}
			PageList pl = new PageList();
			String queryString = "from DdShopgoods where  alive=1 and types=4 "
					+ additionalQuery.toString() + " " + addQuery;
			String maxsizeStr = (" select count(*) " + queryString);
			Connection conn = this.getSession().connection();
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(maxsizeStr.replace("DdShopgoods",
					"dd_shopgoods"));
			int max = 0;
			while (rs.next()) {
				max = rs.getInt(1);
			}
			queryString = queryString + " " + orderby;
			Query q = this.getSession().createQuery(queryString);
			q.setFirstResult((pageNo - 1) * pageSize);
			q.setMaxResults(pageSize);
			List list = q.list();
			pl.addAll(list);
			pl.setTotalCount(max);
			queryString = queryString.substring(0, queryString
					.indexOf("order by"));
			pl.setQueryString(queryString.replace("from DdShopgoods", ""));
			// System.out.println("findbypage use "
			// + (System.currentTimeMillis() - l1));
			return pl;
		} catch (Exception re) {
			re.printStackTrace();
			log.error("findbypage failed", re);
			return null;
		}
	}

	public List findFirstPage(int type) {
		log.debug("finding findrestaurant DdShopgoods instances");
		try {
			String queryString = "from DdFirstPage a,DdShop b where a.shopid=b.id and a.type="
					+ type + " order by a.index, a.id desc";
			return getHibernateTemplate().find(queryString);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}

	public DdFirstPage findFirstPageByShopid(int shopid) {
		log.debug("finding findrestaurant DdShopgoods instances");
		try {
			String queryString = "from DdFirstPage where shopid=" + shopid
					+ " order by id desc";
			List li = getHibernateTemplate().find(queryString);
			if (li.size() != 0) {
				return (DdFirstPage) li.get(0);
			} else {
				return null;
			}
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}

	public void saveOrUpdateFirstPage(DdFirstPage fp) {
		try {
			this.getHibernateTemplate().saveOrUpdate(fp);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}

	public void deleteFirstPage(DdFirstPage fp) {
		log.debug("deleteFirstPage");
		try {
			this.getHibernateTemplate().delete(fp);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}

	public List findAllShopByPos(String posx1, String posy1, String posx2,
			String posy2) {
		log.debug("findAllShopByPos ");
		try {
			return getHibernateTemplate().find(
					"from DdShop where posx>" + posx1 + " and posx<" + posx2
							+ " and  posy>" + posy1 + " and posy<" + posy2
							+ " and alive=1 and types=1");
		} catch (RuntimeException re) {
			log.error("findAllShopByPos failed", re);
			throw re;
		}
	}

	public List findNoGoogle(int count) {

		try {
			List list = getHibernateTemplate().find(
					"from DdShop where posx_g<1 order by address desc");
			list = list.subList(0, count);
			return list;
		} catch (RuntimeException re) {
			log.error("findAllShopByPos failed", re);
			throw re;
		}
	}

	public void test(String str) {

		try {
			String sql = ("insert into lucene_test (keyvalue) VALUES('"+str+"')");
			Connection conn = this.getSession().connection();
			Statement stat = conn.createStatement();
			stat.execute(sql);
		} catch (Exception re) {
			re.printStackTrace();
		}
	}
}